package com.system;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class BaseDao {

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    public JdbcTemplate mysqlJdbc;

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    public JdbcTemplate orclJdbc;


    /**
     * 获取数据
     *
     * @return
     */
    public List<Object> getData(SqlRowSet rs) {
        List<Object> data = new ArrayList<>();
        SqlRowSetMetaData mt = rs.getMetaData();
        List<String> cols = new ArrayList<String>();
        for (int i = 1; i <= mt.getColumnCount(); i++) {
            cols.add(mt.getColumnName(i));
        }

        while (rs.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 0; i < cols.size(); i++) {
                row.put(cols.get(i), rs.getString(cols.get(i)));
            }
            data.add(row);
        }
        return data;
    }

    /**
     * 通过sql 获取数据
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Object> getDataBySql(String sql) throws Exception {
        List<Object> list = null;
        SqlRowSet rs = this.orclJdbc.queryForRowSet(sql);
        list = getData(rs);
        return list;
    }

    /**
     * 通过sql 获取数据 mysql
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Object> getDataBySqlForMysql(String sql) throws Exception {
        List<Object> list = null;
        SqlRowSet rs = this.mysqlJdbc.queryForRowSet(sql);
        list = getData(rs);
        return list;
    }

    /**
     * mysql insert
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public Integer insertCallBackIdForMysql(final String sql) throws Exception {
        List<Object> list = null;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        mysqlJdbc.update(new PreparedStatementCreator() {
                             @Override
                             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                                 PreparedStatement ps = conn.prepareStatement(sql);
                                 ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                                 return ps;
                             }
                         },
                keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * mysql update ind
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public Integer updateForMysql(String sql) throws Exception {
        int num = mysqlJdbc.update(sql);
        return num;
    }

    /**
     * 批量修改 mysql insert update
     *
     * @param sql
     * @param obj
     * @return
     */
    public int[] batchUpdateForMysql(String sql, List<Object[]> obj) {
        int[] ints = mysqlJdbc.batchUpdate(sql, obj);
        return ints;
    }

    /**
     * mysql 查询count
     */
    public Integer queryCountForMysql(String sql) throws Exception {
        Map<String, Object> map = mysqlJdbc.queryForMap(sql);
        Object obj = map.get("count");
        return obj != null ? new Integer(obj.toString()) : 0;
    }


}
